# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import json

import pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
from scrapy.exceptions import DropItem


class BooknetPipeline:
    fp = None

    def open_spider(self, spider):
        self.fp = open('book.json', 'w', encoding='utf-8')

    def process_item(self, item, spider):
        # spider.logger.info(f"item 数据: {item}")
        self.fp.write(json.dumps(ItemAdapter(item).asdict(), ensure_ascii=False) + '\n')
        # 这里需要返回item
        return item

    def close_spider(self, spider):
        self.fp.close()


# 数据服务管道
class MysqlPipeline:

    def __init__(self, host, port, user, password, database, charset, table):
        self.cursor = None
        self.connection = None
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database
        self.charset = charset
        self.table = table

    # from_crawler 方法优先于init方法
    @classmethod
    def from_crawler(cls, crawler):
        return cls(
            host=crawler.settings.get('MYSQL_HOST'),
            port=crawler.settings.get('MYSQL_PORT'),
            user=crawler.settings.get('MYSQL_USER'),
            password=crawler.settings.get('MYSQL_PASSWORD'),
            database=crawler.settings.get('MYSQL_DATABASE'),
            charset=crawler.settings.get('DB_CHARSET'),
            table=crawler.settings.get('MYSQL_TABLE')
        )

    def open_spider(self, spider):
        try:
            # 连接数据库
            self.connection = pymysql.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                database=self.database,
                charset=self.charset,
            )
            self.cursor = self.connection.cursor()
            spider.logger.info("成功连接到数据库")
            # 打印当前使用的数据库
            self.cursor.execute("SELECT DATABASE()")
            db_name = self.cursor.fetchone()[0]
            spider.logger.info(f"当前使用的数据库: {db_name}")
        except Exception as e:
            spider.logger.error(f"数据库连接失败: {e}")
            raise DropItem(f"数据库连接失败: {e}")

    def process_item(self, item, spider):
        # spider.logger.info(f"item 类型: {type(item)}")
        # spider.logger.info(f"item 内容: {item}")
        adapter = ItemAdapter(item)
        # 安全的 SQL 语句
        sql = """
            INSERT INTO {} (title, remark,  author, price, author_remark,status,ibsn_code,cover,publisher,publisher_time)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """.format(self.table)
        price = adapter.get('price')
        try:
            price = float(price[1:]) if price is not None else 0.0
        except ValueError:
            spider.logger.warning(f"Invalid price value: {price}, using default value 0.0")
            price = 0.0

        # 提取 Item 中的数据
        data = (
            adapter.get('title'),
            adapter.get('remark'),
            adapter.get('author'),
            price,
            adapter.get('author_remark'),
            adapter.get('status'),
            adapter.get('ibsn_code'),
            adapter.get('cover'),
            adapter.get('publisher'),
            adapter.get('publisher_time'),
        )
        try:
            # 执行 SQL 语句
            self.cursor.execute(sql, data)
            # 提交事务
            self.connection.commit()
            spider.logger.info(f"数据成功插入数据库: {adapter['title']}")
        except Exception as e:
            # 如果发生错误，回滚事务并记录错误日志
            self.connection.rollback()
            spider.logger.error(f"Failed to insert item into database: {e}")
            raise DropItem(f"Failed to insert item into database: {e}")

        return item

    def close_spider(self, spider):
        # 关闭数据库连接
        self.cursor.close()
        self.connection.close()
